﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using ThuVien;
using System.Data;


namespace TranTekBE
{
    public partial class KPI_QuanLyNhanSu : PageRequireLogin
    {
        public string get_power_user_percent = null;
        public string JsonString = null;
        public string JsonStringColumn = null;
        public string JsonStringSchedule = null;
       
        public float get_number_achievements = 0;
        public string UserName = "";
        public string UserId = "";
        public DateTime get_date_now = DateTime.Now;
        // Lấy ngày hôm nay (m.d.y)
        public string str_get_date_now = DateTime.Now.ToShortDateString();
        // Lấy tháng (m)
        public string str_get_month = DateTime.Now.Month.ToString();
        // Lấy năm (y)
        public string str_get_year = DateTime.Now.Year.ToString();
        // Lấy biến ngày (d)
        string str_day_now = DateTime.Now.Day.ToString();
        //


        protected void Page_Load(object sender, EventArgs e)
        {

            if (UserInfo.AdminKPI == true)
            //
            {
                if (Request["User"] != null && Request["UserName"] != null)
                {
                    UserName = Request["UserName"];
                    UserId = Request["User"];
                }
                else
                {
                    UserName = UserInfo.UserName;
                    UserId = UserInfo.UserID;

                }
            }
            else
            {
                UserName = UserInfo.UserName;
                UserId = UserInfo.UserID;

            }
            //Vẽ chart
            if (Request.QueryString["monthcolumn"] == null && Request.QueryString["monthline"] == null)
            {
                Column_Chart("");
                String_Chart("");
            }
            else
            {
                Column_Chart(Request.QueryString["monthcolumn"]);
                String_Chart(Request.QueryString["monthline"]);
            }
            if (!IsPostBack)
            {
                FunctionsUtils.BindDataForControlList_Month(droplist_Month_1, "", "Chọn tháng xem");
                FunctionsUtils.BindDataForControlList_Month(droplist_Month_2, "", "Chọn tháng xem");
                droplist_Month_2.SelectedValue = Request.QueryString["monthcolumn"];
                droplist_Month_1.SelectedValue = Request.QueryString["monthline"];
                
                // Vẽ lịch
                Schedule_User();
                // News
                News_Latest();
                // Achievements
                Achievements();
                // Avatar
                AvatarUser(UserId);

                //
                string str_sql_user = @"SELECT * FROM tbl_UserKPI
                                    AS UKPI
                                    LEFT JOIN tbl_DanhGiaUser AS UDG
                                    ON UKPI.NAME_USER=UDG.NAME_USER
                                    WHERE UKPI.ID_USER = '" + UserId + @"'";
                DataSet KPI_user = FunctionsUtils.Database2.ExecuteQuery(str_sql_user);
                // Get stores
                if (KPI_user != null && KPI_user.Tables.Count > 0 && KPI_user.Tables[0].Rows.Count > 0)
                {
                    string get_stores_user = KPI_user.Tables[0].Rows[0]["CUA_HANG"].ToString();
                    user_info_store.Text = get_stores_user.ToString();
                    user_name_info.Text = KPI_user.Tables[0].Rows[0]["HO_USER"].ToString() + " " + KPI_user.Tables[0].Rows[0]["TEN_USER"].ToString();

                }
                // Load data Top User
                LoadDataTopUser();
                // Load data manager
                LoadDataManager();
                // Load data Khen Thưởng
                LoadDataKhenThuong();
                // Load data xử phạt
                LoadDataFinerecord();


            }
        }
        // End Page Load----------------------------------------------------------------------------------------------------------

        public string AvatarUser(string userid)
        {
            string fileName = userid + ".png";
            string path_image = Server.MapPath("~/images/KPI/UserImage/") + fileName;
            //
            if (System.IO.File.Exists(path_image))
            {
                avatar_user.Text = "<img style='width: 128px; height: 128px;' alt='' title='' src='../images/KPI/UserImage/" + fileName + "' />";
            }
            else
            {
                avatar_user.Text = "<img style='width: 128px; height: 128px;' alt='' title='' src='../images/KPI/UserImage/avatar_default.png' />";
                    
            }
            return avatar_user.Text.ToString();
        }
        public void String_Chart(string choose_month)
        {
            //
            string date_start_sql = "";
            string date_end_sql = "";
            //
            if (choose_month != null && choose_month != "")
            {
                // Ngày cuối cùng của tháng được chọn
                DateTime dt_choose = new DateTime(Convert.ToInt32(str_get_year), Convert.ToInt32(choose_month), 1);
                dt_choose = dt_choose.AddMonths(1).AddDays(-1);//
                string get_end_day_of_choose_month = dt_choose.Day.ToString();
                ltGetMonth.Text = choose_month;
                ltGetDay.Text = dt_choose.Day.ToString() +"/"+ choose_month;

                //
                date_start_sql = choose_month + "-1-" + str_get_year + "  00:00:00.001";
                date_end_sql = choose_month + "-" + get_end_day_of_choose_month + "-" + str_get_year + "  23:59:59.999";
            }
            else
            {
                // Ngày cuối cùng của tháng hiện tại
                DateTime dt = new DateTime(Convert.ToInt32(str_get_year), Convert.ToInt32(str_get_month), 1);
                dt = dt.AddMonths(1).AddDays(-1);//
                string get_end_day_of_month = dt.Day.ToString();

                ltGetMonth.Text = str_get_month;
                ltGetDay.Text = str_day_now + "/" + str_get_month;

                //
                date_start_sql = str_get_month + "-1-" + str_get_year + "  00:00:00.001";
                date_end_sql = str_get_month + "-" + get_end_day_of_month + "-" + str_get_year + "  23:59:59.999";
            }
            

            // Chuỗi select
            string sql_doanh_thu = null;

            string fromsql = null;

            if (UserId == "TC-17" || UserId == "TC-90" || UserId == "TC-23" || UserId == "TC-69")// ID Chris
            {
                fromsql = "from T0031 where C003100 like '%BH%'";
            }
            else
            {
                fromsql = "from T0031  where UC00002 = '" + UserId + "' and C003100 like '%BH%'";
            }

            //
            sql_doanh_thu = @"select Sum(CASE WHEN datepart(DAY,ABC.UC00001)<=7 THEN C003108 ELSE 0 END) Tuan1 ,
            Sum(CASE WHEN datepart(DAY,ABC.UC00001)>7 and datepart(DAY,ABC.UC00001)<=14 THEN C003108 ELSE 0 END)  Tuan2, 
            Sum(CASE WHEN datepart(DAY,ABC.UC00001)>14 and datepart(DAY,ABC.UC00001)<=21 THEN C003108 ELSE 0 END)  Tuan3, 
            Sum(CASE WHEN datepart(DAY,ABC.UC00001)>21 and datepart(DAY,ABC.UC00001)<=31 THEN C003108 ELSE 0 END) Tuan4 
            from (
            select
            UC00001,
            C003108
            " + fromsql + @"
            AND UC00001 >= '" + date_start_sql + @"'
            AND UC00001 <= '" + date_end_sql + @"'
            ) as ABC
            ";
            double Doanhthu = 0;// Lấy giá trị doanh số bán
            double DoanhThuTuan = 0;
            DataSet doanh_so_user = FunctionsUtils.Database.ExecuteQuery(sql_doanh_thu);// đổ vào dataset DB1
            //
            if (!FunctionsUtils.IsEmpty(doanh_so_user))
            {
                JsonString = @"['Tuần','Doanh Số','Tổng Doanh Số'],";
                for (int i = 1; i <= doanh_so_user.Tables[0].Columns.Count; i++)
                {
                    Doanhthu += FunctionsUtils.ConvertToDouble(doanh_so_user.Tables[0].Rows[0][i - 1].ToString(), 0);
                    DoanhThuTuan = FunctionsUtils.ConvertToDouble(doanh_so_user.Tables[0].Rows[0][i - 1], 0);
                    JsonString += "['Tuần " + i + "'," + DoanhThuTuan + "," + Doanhthu + "],";
                }
                JsonString = JsonString.Substring(0, JsonString.Length - 1);

            }

        }//End function Line Chart-------------------------------------------------------------------------------------
        //-------------------------------------------------------------------------------------------------------------
        public void Column_Chart(string choose_month)
        {
            //
            string date_start_sql = "";
            string date_end_sql = "";
            //
            if (choose_month != null && choose_month != "")
            {
                // Ngày cuối cùng của tháng được chọn
                DateTime dt_choose = new DateTime(Convert.ToInt32(str_get_year), Convert.ToInt32(choose_month), 1);
                dt_choose = dt_choose.AddMonths(1).AddDays(-1);//
                string get_end_day_of_choose_month = dt_choose.Day.ToString();

                lt_danhgia_tieuchuan.Text = choose_month;

                //
                date_start_sql = choose_month + "-1-" + str_get_year + "  00:00:00.001";
                date_end_sql = choose_month + "-" + get_end_day_of_choose_month + "-" + str_get_year + "  23:59:59.999";
            }
            else
            {
                // Ngày cuối cùng của tháng hiện tại
                DateTime dt = new DateTime(Convert.ToInt32(str_get_year), Convert.ToInt32(str_get_month), 1);
                dt = dt.AddMonths(1).AddDays(-1);//
                string get_end_day_of_month = dt.Day.ToString();

                lt_danhgia_tieuchuan.Text = str_get_month;
                //
              date_start_sql   = str_get_month + "-1-" + str_get_year + "  00:00:00.001";
              date_end_sql = str_get_month + "-" + get_end_day_of_month + "-" + str_get_year + "  23:59:59.999";
            }

            // Chuỗi select
            //string sql_noScan = null;
            string sql_danh_gia = null;
            string sql_danh_gia_Chuyen_Mon = null;
            //
            double diemCM1 = 0; double diemCM2 = 0; double diemCM3 = 0; double diemCM4 = 0;
            double diemTD1 = 0; double diemTD2 = 0; double diemTD3 = 0; double diemTD4 = 0;
            double diemGT1 = 0; double diemGT2 = 0; double diemGT3 = 0; double diemGT4 = 0;
            double diemDP1 = 0; double diemDP2 = 0; double diemDP3 = 0; double diemDP4 = 0;
            double diemNL1 = 0; double diemNL2 = 0; double diemNL3 = 0; double diemNL4 = 0;
            double diemKK1 = 0; double diemKK2 = 0; double diemKK3 = 0; double diemKK4 = 0;
            double thang_CM = 0; double thang_TD = 0; double thang_GT = 0;
            double thang_DP = 0; double thang_NL = 0; double thang_KK = 0;
            //

//            // Sql No Scan week
//            sql_noScan = @"
//            select UserId,StoreOfUser,CASHIER,
//             sum( CASE WHEN datepart(DAY,a.Ngay)<=7 THEN 1 ELSE 0 END ) Tuan1 ,
//             sum( CASE WHEN datepart(DAY,a.Ngay)>7 and datepart(DAY,a.Ngay)<=14 THEN 1 ELSE 0 END ) Tuan2, 
//             sum( CASE WHEN datepart(DAY,a.Ngay)>14 and datepart(DAY,a.Ngay)<=21 THEN 1 ELSE 0 END ) Tuan3, 
//             sum( CASE WHEN datepart(DAY,a.Ngay)>21 and datepart(DAY,a.Ngay)<=31 THEN 1 ELSE 0 END ) Tuan4 
//             from (
//                    Select 
//                    trans.C002824 Ngay, 
//                    trans.C002800, 
//                    transline.C003102,
//                    transline.C003100, 
//                    item.C001203, 
//                    item.C001205, 
//                    item.C001204,
//                    item.C001215, 
//                    unit.C004002, 
//                    transline.C003116 a, 
//                    transline.C003117 aa, 
//                    transline.C003130 bb,
//                    transline.C003129 b, 
//                    transline.C003110, 
//                    u.C001905 + ' ' + u.C001904 CASHIER,
//                    u.C001909 StoreOfUser, 
//                    u.C001900 UserId, 
//                    phanloai.C004100 LoaiId,
//                    phanloai.C004101 Loai_VietTat,
//                    phanloai.C004102 LoaiThuoc
//                    from 
//                    [192.168.1.98\CAMSOFT].[POSPRO_20130107].[dbo].[T0031] transline  
//                    Left Join [192.168.1.98\CAMSOFT].[POSPRO_20130107].[dbo].[T0040] unit on transline.C003113 = unit.C004000 
//                    Left Join [192.168.1.98\CAMSOFT].[POSPRO_20130107].[dbo].[T0012] item on transline.C003102 = item.C001200
//                    Left Join [192.168.1.98\CAMSOFT].[POSPRO_20130107].[dbo].[T0017] cate on item.C001201 = cate.C001700 
//                    Left Join [192.168.1.98\CAMSOFT].[POSPRO_20130107].[dbo].[T0028] trans on transline.C003101 = trans.C002800 
//                    Left Join [192.168.1.98\CAMSOFT].[POSPRO_20130107].[dbo].[T0019] u on trans.UC00002 = u.C001900  
//                    left join [192.168.1.98\CAMSOFT].[POSPRO_20130107].[dbo].[T0041] phanloai on phanloai.C004100 = item.C001202  
//                    Where  
//                    trans.C002824>='" + date_start_sql + @"'
//                    AND trans.C002824<'" + date_end_sql + @"'
//                    AND phanloai.C004100 IN ('TC-3','TC-8','THONG-1')
//                    AND u.C001909 !=''
//                    AND C001912 IS NOT NULL 
//                    AND transline.C003117 IS NOT NULL
//                    AND transline.C003130 IS NULL
//                    ) as a
//                    where UserId = '" + UserId + @"'
//                    group by UserId,StoreOfUser,CASHIER
//            ";
//            // Đánh giá Scan Mã
//            DataSet ds_noScan = FunctionsUtils.Database2.ExecuteQuery(sql_noScan);// đổ vào dataset DB2
//            float countscan1 = 0; float countscan2 = 0; float countscan3 = 0; float countscan4 = 0;
//            if (!FunctionsUtils.IsEmpty(ds_noScan))
//            {
//                countscan1 = FunctionsUtils.ConvertToFloat(ds_noScan.Tables[0].Rows[0]["Tuan1"].ToString(), 0);
//                countscan2 = FunctionsUtils.ConvertToFloat(ds_noScan.Tables[0].Rows[0]["Tuan2"].ToString(), 0);
//                countscan3 = FunctionsUtils.ConvertToFloat(ds_noScan.Tables[0].Rows[0]["Tuan3"].ToString(), 0);
//                countscan4 = FunctionsUtils.ConvertToFloat(ds_noScan.Tables[0].Rows[0]["Tuan4"].ToString(), 0);
//            }
            // SQL đánh giá chuyên môn
            sql_danh_gia_Chuyen_Mon = @"SELECT *
            FROM tbl_LichLamViec
            Where ID_USER = '" + UserId + @"' AND
            NGAY_THEM_VAO >= '" + date_start_sql + @"' 
            AND NGAY_THEM_VAO <= '" + date_end_sql + @"' 
            ";
            DataSet ds_trac_nghiem = FunctionsUtils.Database2.ExecuteQuery(sql_danh_gia_Chuyen_Mon);// đổ vào dataset DB2
            DataTable dt_trac_nghiem = ds_trac_nghiem.Tables[0];
            var listTN = dt_trac_nghiem.AsEnumerable().Select(c => new
            {
                ID_USER = c.Field<string>("ID_USER"),
                CUA_HANG_DANG_LAM_VIEC = c.Field<string>("CUA_HANG_DANG_LAM_VIEC"),
                NGAY_THEM_VAO = c.Field<DateTime>("NGAY_THEM_VAO"),
                DiemTracNghiem = c.Field<int>("DiemTracNghiem")
            }).ToList();

            // Tinh trung binh trắc nghiệm tuần
            var TN_Tuan1 = listTN.Where(c => c.NGAY_THEM_VAO.Day <= 7).ToList();
            var TN_Tuan2 = listTN.Where(c => c.NGAY_THEM_VAO.Day > 7 && c.NGAY_THEM_VAO.Day <= 14).ToList();
            var TN_Tuan3 = listTN.Where(c => c.NGAY_THEM_VAO.Day > 14 && c.NGAY_THEM_VAO.Day <= 21).ToList();
            var TN_Tuan4 = listTN.Where(c => c.NGAY_THEM_VAO.Day > 21).ToList();
            if (TN_Tuan1.Count > 0)
            {
                diemCM1 = TN_Tuan1.Average(c => c.DiemTracNghiem);
            }

            if (TN_Tuan2.Count > 0)
            {
                diemCM2 = TN_Tuan2.Average(c => c.DiemTracNghiem);
            }

            if (TN_Tuan3.Count > 0)
            {
                diemCM3 = TN_Tuan3.Average(c => c.DiemTracNghiem);
            }

            if (TN_Tuan4.Count > 0)
            {
                diemCM4 = TN_Tuan4.Average(c => c.DiemTracNghiem);
            }


            // Đánh giá điểm 
            sql_danh_gia = @"SELECT * 
            FROM tbl_DanhGiaUser where ID_USER = '" + UserId + @"' AND
            NGAY_TAO >= '" + date_start_sql + @"' 
            AND NGAY_TAO <= '" + date_end_sql + @"' 
            ";
            DataSet ds_danh_gia = FunctionsUtils.Database2.ExecuteQuery(sql_danh_gia);// đổ vào dataset DB2
            DataTable dt_danh_gia = ds_danh_gia.Tables[0];
            var listDanhGia = dt_danh_gia.AsEnumerable().Select(c => new
            {
                DANH_GIA_DONG_PHUC = c.Field<int>("DANH_GIA_DONG_PHUC"),
                DANH_GIA_GIAO_TIEP = c.Field<int>("DANH_GIA_GIAO_TIEP"),
                DANH_GIA_NANG_LUONG = c.Field<int>("DANH_GIA_NANG_LUONG"),
                DANH_GIA_THAI_DO = c.Field<int>("DANH_GIA_THAI_DO"),
                DANH_GIA_KIEM_KE = c.Field<int>("DANH_GIA_KIEM_KE"),
                NGAY_TAO = c.Field<DateTime>("NGAY_TAO"),
            }).ToList();
            

            // Tinh trung binh tuan
            var listTuan1 = listDanhGia.Where(c => c.NGAY_TAO.Day <= 7).ToList();
            var listTuan2 = listDanhGia.Where(c =>c.NGAY_TAO.Day>7&& c.NGAY_TAO.Day <= 14).ToList();
            var listTuan3 = listDanhGia.Where(c => c.NGAY_TAO.Day >14&&c.NGAY_TAO.Day<=21).ToList();
            var listTuan4 = listDanhGia.Where(c => c.NGAY_TAO.Day >21).ToList();
            if (listTuan1.Count > 0)
            {
                diemTD1 = listTuan1.Average(c => c.DANH_GIA_THAI_DO);
                diemGT1 = listTuan1.Average(c => c.DANH_GIA_GIAO_TIEP);
                diemDP1 = listTuan1.Average(c => c.DANH_GIA_DONG_PHUC);
                diemNL1 = listTuan1.Average(c => c.DANH_GIA_NANG_LUONG);
                diemKK1 = listTuan1.Average(c => c.DANH_GIA_KIEM_KE);
            }
            if (listTuan2.Count > 0)
            {
                diemTD2 = listTuan2.Average(c => c.DANH_GIA_THAI_DO);
                diemGT2 = listTuan2.Average(c => c.DANH_GIA_GIAO_TIEP);
                diemDP2 = listTuan2.Average(c => c.DANH_GIA_DONG_PHUC);
                diemNL2 = listTuan2.Average(c => c.DANH_GIA_NANG_LUONG);
                diemKK2 = listTuan2.Average(c => c.DANH_GIA_KIEM_KE);
            }
            if (listTuan3.Count > 0)
            {
                diemTD3 = listTuan3.Average(c => c.DANH_GIA_THAI_DO);
                diemGT3 = listTuan3.Average(c => c.DANH_GIA_GIAO_TIEP);
                diemDP3 = listTuan3.Average(c => c.DANH_GIA_DONG_PHUC);
                diemNL3 = listTuan3.Average(c => c.DANH_GIA_NANG_LUONG);
                diemKK3 = listTuan3.Average(c => c.DANH_GIA_KIEM_KE);
            }
            if (listTuan4.Count > 0)
            {
                diemTD4 = listTuan4.Average(c => c.DANH_GIA_THAI_DO);
                diemGT4 = listTuan4.Average(c => c.DANH_GIA_GIAO_TIEP);
                diemDP4 = listTuan4.Average(c => c.DANH_GIA_DONG_PHUC);
                diemNL4 = listTuan4.Average(c => c.DANH_GIA_NANG_LUONG);
                diemKK4 = listTuan4.Average(c => c.DANH_GIA_KIEM_KE);
            }
            // Tinh trung  binh thang
            
            thang_CM =listTN.Count>0?  listTN.Average(c => c.DiemTracNghiem):0;// Trắc Nghiệm
            thang_DP =listDanhGia.Count>0?  listDanhGia.Average(c => c.DANH_GIA_DONG_PHUC):0;
            thang_GT =listDanhGia.Count>0? listDanhGia.Average(c => c.DANH_GIA_GIAO_TIEP):0;
            thang_KK =listDanhGia.Count>0? listDanhGia.Average(c => c.DANH_GIA_KIEM_KE):0;
            thang_NL =listDanhGia.Count>0? listDanhGia.Average(c => c.DANH_GIA_NANG_LUONG):0;
            thang_TD =listDanhGia.Count>0? listDanhGia.Average(c => c.DANH_GIA_THAI_DO):0;

            // Lấy giá trị cho thanh progessbar
             string phantram = ((thang_CM + thang_DP + thang_GT + thang_KK + thang_NL + thang_TD) * 100 / 60).ToString("####0");
             ltpower_user.Text = phantram;
            // Lấy giá trị progessbar
             get_power_user_percent = phantram;


            JsonStringColumn = @"{'res':
                                    [
                                    {'time':'Tuần 1','chuyenmon':" + string.Format("{0:0.0}",diemCM1) + @",'thaido':" + string.Format("{0:0.0}",diemTD1) + @",'giaotiep':" + string.Format("{0:0.0}",diemGT1) + @",'dongphuc':" + string.Format("{0:0.0}",diemDP1) + @",'nangluong':" + string.Format("{0:0.0}",diemNL1) + @",'kiemke':" + string.Format("{0:0.0}",diemKK1) + @"},
                                    {'time':'Tuần 2','chuyenmon':" + string.Format("{0:0.0}",diemCM2) + @",'thaido':" + string.Format("{0:0.0}",diemTD2) + @",'giaotiep':" + string.Format("{0:0.0}",diemGT2) + @",'dongphuc':" + string.Format("{0:0.0}",diemDP2) + @",'nangluong':" + string.Format("{0:0.0}",diemNL2) + @",'kiemke':" + string.Format("{0:0.0}",diemKK2) + @"},
                                    {'time':'Tuần 3','chuyenmon':" + string.Format("{0:0.0}",diemCM3) + @",'thaido':" + string.Format("{0:0.0}",diemTD3) + @",'giaotiep':" + string.Format("{0:0.0}",diemGT3) + @",'dongphuc':" + string.Format("{0:0.0}",diemDP3) + @",'nangluong':" + string.Format("{0:0.0}",diemNL3) + @",'kiemke':" + string.Format("{0:0.0}",diemKK3) + @"},
                                    {'time':'Tuần 4','chuyenmon':" + string.Format("{0:0.0}",diemCM4) + @",'thaido':" + string.Format("{0:0.0}",diemTD4) + @",'giaotiep':" + string.Format("{0:0.0}",diemGT4) + @",'dongphuc':" + string.Format("{0:0.0}",diemDP4) + @",'nangluong':" + string.Format("{0:0.0}",diemNL4) + @",'kiemke':" + string.Format("{0:0.0}",diemKK4) + @"},
                                    {'time':'Trung bình (Tháng " + str_get_month + @")','chuyenmon':" + string.Format("{0:0.0}", thang_CM) + @",'thaido':" + string.Format("{0:0.0}", thang_TD) + @",'giaotiep':" + string.Format("{0:0.0}", thang_GT) + @",'dongphuc':" + string.Format("{0:0.0}", thang_DP) + @",'nangluong':" + string.Format("{0:0.0}", thang_NL) + @",'kiemke':" + string.Format("{0:0.0}", thang_KK) + @"}
                                    ]
                                    }";// 2 cột hoặc dòng


        }//end Column chart--------------------------------------------------------------------------------------------
        //-------------------------------------------------------------------------------------------------------------
        public void Schedule_User()
        {
            // Lấy 1 tháng trước now (m)
            string str_get_before_month = (Convert.ToInt32(get_date_now.Month) - 1).ToString();

            //
            DateTime dtime = new DateTime(Convert.ToInt32(str_get_year), Convert.ToInt32(str_get_month), 1);
            dtime = dtime.AddMonths(1).AddDays(-1);//
            string get_end_day_of_month = dtime.Day.ToString();
            // End get day
            //
            string sql_schedule_user = @"
                SELECT *  FROM tbl_LichLamViec WHERE ID_USER = '" + UserId + @"'
                and NGAY_THEM_VAO >= '" + str_get_before_month + @"/1/" + str_get_year + @"  00:00:00.001' 
                and NGAY_THEM_VAO < = '" + DateTime.Now.AddMonths(2).AddDays(-1).ToShortDateString() + @"  23:59:59.999' 
                order by NGAY_THEM_VAO asc
                ";
            DataSet ds_schedule_user = FunctionsUtils.Database2.ExecuteQuery(sql_schedule_user);
            JsonStringSchedule = " ";
            foreach (DataRow row in ds_schedule_user.Tables[0].Rows)
            {
                string IdLich = row["ID_LICH_LAM_VIEC"].ToString();
                string cua_hang = row["CUA_HANG_DANG_LAM_VIEC"].ToString();
                string ca_lam = row["CA_LAM_VIEC"].ToString();
                string thoi_gian = row["NGAY_THEM_VAO"].ToString();

                //JsonStringSchedule += "{title:'" + user_info + " " + "-Ca:" + ca_lam + "-" + cua_hang + "',start:'" + thoi_gian + "',id:'" + ID_Lich + "'},";
                JsonStringSchedule += "{title:'Ca:" + ca_lam + "-" + cua_hang + "',start:'" + thoi_gian + "',id:'" + IdLich + "'},";

            }

            JsonStringSchedule = JsonStringSchedule.Substring(0, JsonStringSchedule.Length - 1);
        }
        //-------------------------------------------------------------------------------------------------------------
        // Xử phạt-------------------------------------------------------------------------------------------------------------
        void LoadDataFinerecord()
        {
            //
            DateTime dtime = new DateTime(Convert.ToInt32(str_get_year), Convert.ToInt32(str_get_month), 1);
            dtime = dtime.AddMonths(1).AddDays(-1);//
            string get_end_day_of_month = dtime.Day.ToString();

            DataSet user_Finerecord = FunctionsUtils.Database2.ExecuteQuery(@"SELECT * FROM tbl_XuPhat 
                                                                            WHERE NGAY_PHAT >= '" + str_get_month + @"/1/" + str_get_year + @"  00:00:00.001' and
                                                                            NGAY_PHAT < = '" + str_get_month + @"/" + get_end_day_of_month + @"/" + str_get_year + @"  23:59:59.999'
                                                                            and ID_USER='" + UserId + @"'
                                                                            Order by NGAY_PHAT asc
                                                                            ");

            DataTable dt = user_Finerecord.Tables[0];

            PagedDataSource pgitems = new PagedDataSource();

            System.Data.DataView dv = new System.Data.DataView(dt);

            pgitems.DataSource = dv;

            pgitems.AllowPaging = true;

            pgitems.PageSize = 5;

            pgitems.CurrentPageIndex = PageNumber;

            if (pgitems.PageCount > 1)
            {

                rptPages.Visible = true;

                System.Collections.ArrayList pages = new System.Collections.ArrayList();

                for (float i = 0; i < pgitems.PageCount; i++)

                    pages.Add((i + 1).ToString());

                rptPages.DataSource = pages;

                rptPages.DataBind();

            }

            else

            { rptPages.Visible = false; }

            rpFinerecord.DataSource = pgitems;

            rpFinerecord.DataBind();

        }

        public int PageNumber
        {

            get
            {

                if (ViewState["PageNumber"] != null)

                    return Convert.ToInt32(ViewState["PageNumber"]);

                else

                    return 0;

            }

            set
            {

                ViewState["PageNumber"] = value;

            }

        }

        protected void rptPages_ItemCommand1(object source, RepeaterCommandEventArgs e)
        {

            PageNumber = Convert.ToInt32(e.CommandArgument) - 1;

            LoadDataFinerecord();

        }
        //-------------------------------------------------------------------------------------------------------------
        // Đánh giá bình luận-----------------------------------------------------------------------------------------------------------
        void LoadDataManager()
        {
            DateTime dtime = new DateTime(Convert.ToInt32(str_get_year), Convert.ToInt32(str_get_month), 1);
            dtime = dtime.AddMonths(1).AddDays(-1);//
            string get_end_day_of_month = dtime.Day.ToString();

            DataSet user_Manager = FunctionsUtils.Database2.ExecuteQuery(@"SELECT * FROM tbl_DanhGiaUser 
                                                                            WHERE NGAY_TAO >= '" + str_get_month + @"/1/" + str_get_year + @"  00:00:00.001' and
                                                                            NGAY_TAO < = '" + str_get_month + @"/" + get_end_day_of_month + @"/" + str_get_year + @"  23:59:59.999'
                                                                            and ID_USER='" + UserId + @"'
                                                                            and BINH_LUAN != ''
                                                                            Order by NGAY_TAO asc
                                                                                                        ");


            DataTable dt1 = user_Manager.Tables[0];

            PagedDataSource pgitems1 = new PagedDataSource();

            System.Data.DataView dv1 = new System.Data.DataView(dt1);

            pgitems1.DataSource = dv1;

            pgitems1.AllowPaging = true;

            pgitems1.PageSize = 5;

            pgitems1.CurrentPageIndex = PageNumber;

            if (pgitems1.PageCount > 1)
            {

                rptPages_Manager.Visible = true;

                System.Collections.ArrayList pages1 = new System.Collections.ArrayList();

                for (float i = 0; i < pgitems1.PageCount; i++)

                    pages1.Add((i + 1).ToString());

                rptPages_Manager.DataSource = pages1;

                rptPages_Manager.DataBind();

            }

            else

            { rptPages_Manager.Visible = false; }

            rpManager.DataSource = pgitems1;

            rpManager.DataBind();

        }



        protected void rptPages_ItemCommand_Manager(object source, RepeaterCommandEventArgs e)
        {

            PageNumber = Convert.ToInt32(e.CommandArgument) - 1;

            LoadDataManager();

        }
        //-------------------------------------------------------------------------------------------------------------
        // Top User-------------------------------------------------------------------------------------------------------------
        void LoadDataTopUser()
        {
            DateTime dtime = new DateTime(Convert.ToInt32(str_get_year), Convert.ToInt32(str_get_month), 1);
            dtime = dtime.AddMonths(1).AddDays(-1);//
            string get_end_day_of_month = dtime.Day.ToString();
            string sql = @"
Exec GetUserTop 
@Startdate = '" + str_get_month + @"/1/" + str_get_year + @"  00:00:00.001' , 
@Enddate = '" + str_get_month + @"/" + get_end_day_of_month + @"/" + str_get_year + @"  23:59:59.999'";

            DataSet user_Top = FunctionsUtils.Database2.ExecuteQuery(sql);

            DataTable dttop = user_Top.Tables[0];

            PagedDataSource pgitemstop = new PagedDataSource();

            System.Data.DataView dvtop = new System.Data.DataView(dttop);

            pgitemstop.DataSource = dvtop;

            pgitemstop.AllowPaging = true;

            pgitemstop.PageSize = 5;

            pgitemstop.CurrentPageIndex = PageNumber;

            if (pgitemstop.PageCount > 1)
            {

                rptPages_TopUser.Visible = true;

                System.Collections.ArrayList pagestop = new System.Collections.ArrayList();

                for (float i = 0; i < pgitemstop.PageCount; i++)

                    pagestop.Add((i + 1).ToString());

                rptPages_TopUser.DataSource = pagestop;

                rptPages_TopUser.DataBind();

            }

            else

            { rptPages_TopUser.Visible = false; }

            rpTopUser.DataSource = pgitemstop;

            rpTopUser.DataBind();

        }

        protected void rptPages_ItemCommand_TopUser(object source, RepeaterCommandEventArgs e)
        {

            PageNumber = Convert.ToInt32(e.CommandArgument) - 1;

            LoadDataTopUser();

        }
        //-------------------------------------------------------------------------------------------------------------
        // Khen Thuong-------------------------------------------------------------------------------------------------------------
        void LoadDataKhenThuong()
        {
            DateTime dtime = new DateTime(Convert.ToInt32(str_get_year), Convert.ToInt32(str_get_month), 1);
            dtime = dtime.AddMonths(1).AddDays(-1);//
            string get_end_day_of_month = dtime.Day.ToString();

            DataSet user_Khen = FunctionsUtils.Database2.ExecuteQuery(@"SELECT * FROM tbl_KhenThuong 
                                                                            WHERE NGAY_KHEN_THUONG >= '" + str_get_month + @"/1/" + str_get_year + @"  00:00:00.001' and
                                                                            NGAY_KHEN_THUONG < = '" + str_get_month + @"/" + get_end_day_of_month + @"/" + str_get_year + @"  23:59:59.999'
                                                                            and ID_USER='" + UserId + @"'");


            DataTable dtkhenthuong = user_Khen.Tables[0];

            PagedDataSource pgitemskhenthuong = new PagedDataSource();

            System.Data.DataView dvkhenthuong = new System.Data.DataView(dtkhenthuong);

            pgitemskhenthuong.DataSource = dvkhenthuong;

            pgitemskhenthuong.AllowPaging = true;

            pgitemskhenthuong.PageSize = 5;

            pgitemskhenthuong.CurrentPageIndex = PageNumber;

            if (pgitemskhenthuong.PageCount > 1)
            {

                rptPages_KhenThuong.Visible = true;

                System.Collections.ArrayList pageskhenthuong = new System.Collections.ArrayList();

                for (float i = 0; i < pgitemskhenthuong.PageCount; i++)

                    pageskhenthuong.Add((i + 1).ToString());

                rptPages_KhenThuong.DataSource = pageskhenthuong;

                rptPages_KhenThuong.DataBind();

            }

            else

            { rptPages_KhenThuong.Visible = false; }

            rpKhenThuong.DataSource = pgitemskhenthuong;

            rpKhenThuong.DataBind();

        }

        protected void rptPages_ItemCommand_KhenThuong(object source, RepeaterCommandEventArgs e)
        {

            PageNumber = Convert.ToInt32(e.CommandArgument) - 1;

            LoadDataKhenThuong();

        }
        //-------------------------------------------------------------------------------------------------------------
        // -------------------------------------------------------------------------------------------------------------
        public void Achievements()
        {

            DateTime dt = new DateTime(Convert.ToInt32(str_get_year), Convert.ToInt32(str_get_month), 1);
            dt = dt.AddMonths(1).AddDays(-1);
            //Lấy day cuối tháng
            string get_end_day_of_month = dt.Day.ToString();
            //

            string sql_achievements = @"select * from tbl_KhenThuong
                                where ID_USER = '" + UserId + @"'
                                and NGAY_KHEN_THUONG >= '" + str_get_month + "/1/" + str_get_year + @"'
                                and NGAY_KHEN_THUONG <= '" + str_get_month + "/" + get_end_day_of_month + "/" + str_get_year + @"'
                                   ";


            DataSet ds_achievements = FunctionsUtils.Database2.ExecuteQuery(sql_achievements);
            // Get achievements
            string get_Achievements = "";
            string get_title = "";
            if (ds_achievements != null && ds_achievements.Tables.Count > 0 && ds_achievements.Tables[0].Rows.Count > 0)
            {
                for (int i = 0; i < ds_achievements.Tables[0].Rows.Count; i++)
                {
                    get_title = ds_achievements.Tables[0].Rows[i]["EVENT"].ToString();
                    get_Achievements = ds_achievements.Tables[0].Rows[i]["ID_EVENT"].ToString();
                    ltlDivDynamic_img.Text += @"
                <img style='width:90px;margin-left:5px;' src='../images/KPI/Achievements/" + get_Achievements + @".png' alt='" + get_title + @"' title='" + get_title + @"' />
                ";
                }
                get_number_achievements = ds_achievements.Tables[0].Rows.Count;

            }
            else
            {
                get_number_achievements = 0;

            }


        }
        void News_Latest()
        {
            string sql_select_news = @"SELECT Top 3 ID,Title,NgayGui,NguoiGui
                                    FROM tbl_News
                                    Order By ID desc";
            string id_news = "";
            string title_news = "";
            string news_moi_nhat = "<img style='float:left' alt = 'Tin mới nhất' src='../images/KPI/new_news.png' />";
            string tag_a = "";
            DataSet ds_news = FunctionsUtils.Database2.ExecuteQuery(sql_select_news);
            for (int i = 0; i < ds_news.Tables[0].Rows.Count; i++)
            {
                id_news = ds_news.Tables[0].Rows[i]["ID"].ToString();
                title_news = ds_news.Tables[0].Rows[i]["Title"].ToString();
                if (i == 0)
                {
                    tag_a = "<a style='float:left' href='../News/Read.aspx?id=" + id_news + "'><i>" + title_news + @"</i></a> " + news_moi_nhat;
                }
                else
                {
                    tag_a = "<a href='../News/Read.aspx?id=" + id_news + "'><i>" + title_news + @"</i></a> ";
                }
                ltlDivDynamic_news.Text += @"
                <div class='swiper-slide'>
	                " + tag_a + @"
                </div>
                ";
            }

        }

        protected void droplist_Month_1_SelectedIndexChanged(object sender, EventArgs e)
        {
            Response.Redirect("KPI-QuanLyNhanSu.aspx?monthcolumn=" + droplist_Month_2.SelectedValue.ToString() + "&monthline=" + droplist_Month_1.SelectedValue.ToString() + "&User=" + UserId + "&UserName=" + UserName);
        }

        protected void droplist_Month_2_SelectedIndexChanged(object sender, EventArgs e)
        {
            Response.Redirect("KPI-QuanLyNhanSu.aspx?monthcolumn=" + droplist_Month_2.SelectedValue.ToString() + "&monthline=" + droplist_Month_1.SelectedValue.ToString() + "&User=" + UserId + "&UserName=" + UserName);
        }

    }
}